import pandas as pd
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from models import *  # 确保从models.py导入正确的模型
import os
import random
from datetime import datetime, timedelta
from models import db, Product, MaintainRecord
from models import Product, InspectionRecord
from sqlalchemy import Table, MetaData



def import_org_data():
    db_path = os.path.abspath('instance/mis.db')
    engine = create_engine(f'sqlite:///{db_path}')
    Session = sessionmaker(bind=engine)
    session = Session()
    try:
        # 读取Excel文件
        file_path = os.path.abspath('xls/assets.xlsx')
        df = pd.read_excel(file_path, sheet_name='orgs', header=0)
        # print(df)
        # 维修状态映射字典
        
        new_records = []
        
        for index, row in df.iterrows():
        
           
            
            new_records.append(Organization(
                
                
               
                org_name = row['org_name'],
                org_code = row['org_code'],
                org_level = row['org_level'],
            	is_active = row['is_active'],
                parent_id = row['parent_id'],
                address = row['address'],
                contact_person = row['contact_person'],
                contact_phone = row['contact_phone'],
                short_name = row['short_name'],
                short_name_c = row['short_name_c'],


                
              
            ))

        # 批量插入数据
        session.bulk_save_objects(new_records)
        session.commit()
        print(f"成功导入 {len(new_records)} 条机构记录")
        
    except Exception as e:
        session.rollback()
        print(f"导入失败:{str(e)} ")
    finally:
        session.close()



def import_department_data():
    db_path = os.path.abspath('instance/mis.db')
    engine = create_engine(f'sqlite:///{db_path}')
    Session = sessionmaker(bind=engine)
    session = Session()
    try:
        # 读取Excel文件
        file_path = os.path.abspath('xls/assets.xlsx')
        df = pd.read_excel(file_path, sheet_name='departments', header=0)
        # print(df)
        # 维修状态映射字典
        
        new_records = []
        
        for index, row in df.iterrows():
        
           
            
            new_records.append(Department(
                dept_code = row['department_code'],
                org_name = row['org_name'],
                org_id = row['org_id'],
                dept_name = row['department_name'],
                # is_active = row['is_active'],
                # department_level = row['department_level'],
                # parent = row['parent'],
                # address = row['address'],
                # contact_person = row['contact_person'],
                # contact_phone = row['contact_phone'],

            ))

        # 批量插入数据
        session.bulk_save_objects(new_records)
        session.commit()
        print(f"成功导入 {len(new_records)} 条部门记录")
        
    except Exception as e:
        session.rollback()
        print(f"导入失败:{str(e)} ")
    finally:
        session.close()




# def import_product_data():
#     db_path = os.path.abspath('instance/mis.db')
#     engine = create_engine(f'sqlite:///{db_path}')
#     Session = sessionmaker(bind=engine)
#     session = Session()
#     try:
#         # 读取Excel文件
#         file_path = os.path.abspath('xls/assets.xlsx')
#         df = pd.read_excel(file_path, sheet_name='products', header=0)
#         # df = df.head(218)
#         # print(df)
#         # 维修状态映射字典
        
#         new_records = []
        
#         for index, row in df.iterrows():
#             try:
           
            
#                 new_records.append(Product(
#                     # 修复字段映射，移除repair_id字段
#                     # asset_id = str(row['asset_id']) if pd.notna(row['asset_id']) else '',  # 转为字符串
#                     product_name = row['asset_name'],
#                     product_code = str(row['asset_code']) if pd.notna(row['asset_code']) else '',
                    
                    
#                     equipment_category = str(row['category']) if pd.notna(row['category']) else '',
#                     price = pd.to_numeric(row['price']) if pd.notna(row['price']) else 0.0,
#                     purchase_date = pd.to_datetime(row['purchase_date']).date() if pd.notna(row['purchase_date']) else None,
#                     # annual_maintenance_plan = int(row['annual_maintenance_plan']) if pd.notna(row['annual_maintenance_plan']) else 0,
#                     # last_inspection_date = pd.to_datetime(row['last_inspection_date']).date() if pd.notna(row['last_inspection_date']) else None,
#                     # last_repair_date = pd.to_datetime(row['last_repair_date']).date() if pd.notna(row['last_repair_date']) else None,
#                     # last_maintenance_date = pd.to_datetime(row['last_maintenance_date']).date() if pd.notna(row['last_maintenance_date']) else None,
#                     manufacturer = row['brand'],
#                     # standard = str(row['standard']) if pd.notna(row['standard']) else '',  # 处理字符串字段
#                     service_life = int(float(row['service_life'])) if pd.notna(row['service_life']) else 0,
#                     running_status = str(row['status']) if pd.notna(row['status']) else '',
#                     # is_active = bool(pd.to_numeric(row['is_active'], errors='coerce') == 1) if pd.notna(row['is_active']) else False,  # 强化类型转换
#                     org_name = str(row['org_name']) if pd.notna(row['org_name']) else '',
#                     org_short_name_c = str(row['org_short_name_c']) if pd.notna(row['org_short_name_c']) else '',
#                     department_name = row['department_name'],
#                     # location = None,
#                     # maintenance_level = None,
                
#                     # account_holder_name = None,
#                     # account_holder_code = None,
#                     # user_name = None,
#                     # user_code = None,
#                     # manufacturer = None,
                    
#                     # commissioning_date = None,
#                     # supplier_code = None,
#                     # supplier_name = None,
#                     # is_enabled = None,
#                     # annual_inspection_frequency = None,
                    
#                     # inspection_plan_name = None,
                
#                     # submitter = None,
#                     # submit_time = None,
#                     # update_time = None,
                    
              
#                     # location = row['location'],
#                     # maintenance_level = row['maintenance_level'],
                
#                     # account_holder_name = row['account_holder_name'],
#                     # account_holder_code = row['account_holder_code'],
#                     # user_name = row['user_name'],
#                     # user_code = row['user_code'],
#                     # manufacturer = row['manufacturer'],
                    
#                     # commissioning_date = pd.to_datetime(row['commissioning_date']).date() if pd.notna(row['commissioning_date']) else None,
#                     # supplier_code = row['supplier_code'],
#                     # supplier_name = row['supplier_name'],
#                     # is_enabled = row['is_enabled'],
#                     # annual_inspection_frequency = row['annual_inspection_frequency'],
                    
#                     # inspection_plan_name = row['inspection_plan_name'],
                
#                     # submitter = row['submitter'],
#                     # submit_time = pd.to_datetime(row['submit_time']).date() if pd.notna(row['submit_time']) else None,
#                     # update_time = pd.to_datetime(row['update_time']).date() if pd.notna(row['update_time']) else None,
                    
              
              
#                 ))
#             except Exception as e:
#                 print(f"第{index+2}行数据错误（Excel行号）：")
#                 print(f"错误字段：is_active 值：{row['is_active']}")
#                 print(f"错误详情：{str(e)}")
#                 raise    

#         # 批量插入数据
#         session.bulk_save_objects(new_records)
#         session.commit()
#         print(f"成功导入 {len(new_records)} 条资产记录")
        
#     except Exception as e:
#         session.rollback()
#         # session.commit()

#         print(f"导入失败:{str(e)} ")
#     finally:
#         session.close()


# def import_repair_data():
#     db_path = os.path.abspath('instance/mis.db')
#     engine = create_engine(f'sqlite:///{db_path}')
#     Session = sessionmaker(bind=engine)
#     session = Session()
#     try:
#         # 读取Excel文件
#         file_path = os.path.abspath('xls/repairelist.xlsx')
#         df = pd.read_excel(file_path, sheet_name='sheet1', header=0)
#         # print(df)
#         # 维修状态映射字典
        
#         new_records = []
        
#         for index, row in df.iterrows():
        
#             try:
#                 repair_duration = float(row['repair_duration']) if pd.notna(row['repair_duration']) else 0
#             except ValueError:
#                 repair_duration = 0

            
#             new_records.append(RepairRecord(
                
                
               
#                 report_date = pd.to_datetime(row['报修时间']).date() if pd.notna(row['报修时间']) else None,
#                 org_name = row['医院名称'],
#                 org_shortname_c = row['org_shortname_c'],
#                 department_name = row['所属科室'],
#                 product_name = row['设备名称'],
#                 product_code = row['资产编号'],
#                 product_category = row['通用类型'],
#                 product_brand = row['品牌'],
#                 product_model = row['型号'],
#                 fault_description = row['故障描述'],
#                 repair_status = row['维修状态'],
#                 repair_content = row['维修内容'],
                


#                 repair_duration = round(repair_duration,2)
                
                

#             ))

#         # 批量插入数据
#         session.bulk_save_objects(new_records)
#         session.commit()
#         print(f"成功导入 {len(new_records)} 条维修记录")
        
#     except Exception as e:
#         session.rollback()
#         print(f"导入失败:{str(e)} ")
#     finally:
#         session.close()

# def import_repair_data2():
#     import random
#     from datetime import datetime, timedelta

#     db_path = os.path.abspath('instance/mis.db')
#     engine = create_engine(f'sqlite:///{db_path}')
#     Session = sessionmaker(bind=engine)
#     session = Session()
#     try:
#         products = session.query(Product).all()
#         if not products:
#             print("无可用设备数据")
#             return

#         now = datetime.now()
#         start_time = datetime(2025, 1, 1, 0, 0, 0)

#         repair_callers = ['张三', '李四', '王五', '赵六', '钱七', '孙八']
#         repair_types = ['日常维修', '紧急维修', '预防性维修', '故障维修']
#         fault_descriptions = ['无法开机', '噪音异常', '显示故障', '电源故障', '按钮失灵', '温度异常']
#         repair_results = ['修复完成', '更换部件', '无法修复', '待观察']
#         repair_contents = ['更换主板', '清理灰尘', '调整参数', '更换电源', '更换显示屏']
#         repair_persons = ['工程师A', '工程师B', '工程师C', '工程师D']
#         repair_companies = ['本院', '厂家', '第三方公司']
#         replaced_parts = ['主板', '电源', '显示屏', '按钮', '无']
#         remarks_list = ['处理及时', '用户满意', '建议加强维护', '多次故障', '首次报修']

#         status_choices = (
#             ['待响应'] * 1 +
#             ['维修中'] * 2 +
#             ['已完成'] * 17
#         )  # 控制比例，可根据实际需求调整

#         new_records = []
#         for _ in range(500):
#             product = random.choice(products)
#             repair_status = random.choice(status_choices)

#             # 公共字段
#             base_kwargs = dict(
#                 product_id=product.id,
#                 product_name=product.product_name,
#                 product_code=product.product_code,
#                 product_category=getattr(product, 'equipment_category', ''),
#                 product_brand=getattr(product, 'manufacturer', ''),
#                 product_model=getattr(product, 'model', ''),
#                 org_name=getattr(product, 'org_name', ''),
#                 org_shortname_c=getattr(product, 'org_short_name_c', ''),
#                 department_name=getattr(product, 'department_name', ''),
#                 repair_status=repair_status,
#                 is_active=True
#             )

#             # 生成时间
#             report_date = start_time + timedelta(seconds=random.randint(0, int((now - start_time).total_seconds())))
#             answer_delta = timedelta(hours=random.uniform(1, 24))
#             repair_answer_date = report_date + answer_delta
#             expected_delta = timedelta(days=random.uniform(1, 5))
#             expected_completion_date = (repair_answer_date + expected_delta).date()
#             complete_delta = timedelta(days=random.uniform(0, 2), hours=random.uniform(0, 23), minutes=random.uniform(0, 59))
#             actual_completion_date = datetime.combine(expected_completion_date, datetime.min.time()) + complete_delta

#             repair_answer_duration = round((repair_answer_date - report_date).total_seconds() / 3600, 2)
#             repair_duration = round((actual_completion_date - report_date).total_seconds() / 3600, 2)
#             repair_cost = round(random.uniform(100, 2000), 2)

#             # 根据状态填充字段
#             if repair_status == '待响应':
#                 record = RepairRecord(
#                     **base_kwargs,
#                     repair_caller=random.choice(repair_callers),
#                     report_date=report_date,
#                     fault_description=random.choice(fault_descriptions),
#                     create_time=report_date,
#                     update_time=report_date
#                 )
#                 # 状态为待响应，设备设为运行中
#                 product.running_status = 1
#             elif repair_status == '维修中':
#                 record = RepairRecord(
#                     **base_kwargs,
#                     repair_caller=random.choice(repair_callers),
#                     report_date=report_date,
#                     fault_description=random.choice(fault_descriptions),
#                     repair_answer_date=repair_answer_date,
#                     repair_person=random.choice(repair_persons),
#                     repair_company=random.choice(repair_companies),
#                     expected_completion_date=expected_completion_date,
#                     remarks=random.choice(remarks_list),
#                     repair_answer_duration=repair_answer_duration,
#                     create_time=report_date,
#                     update_time=repair_answer_date
#                 )
#                 # 状态为维修中，设备设为运行中
#                 product.running_status = 1
#             else:  # 已完成 或 已取消
#                 record = RepairRecord(
#                     **base_kwargs,
#                     repair_caller=random.choice(repair_callers),
#                     report_date=report_date,
#                     fault_description=random.choice(fault_descriptions),
#                     repair_answer_date=repair_answer_date,
#                     repair_person=random.choice(repair_persons),
#                     repair_company=random.choice(repair_companies),
#                     expected_completion_date=expected_completion_date,
#                     remarks=random.choice(remarks_list),
#                     repair_answer_duration=repair_answer_duration,
#                     repair_type=random.choice(repair_types),
#                     repair_result=random.choice(repair_results),
#                     repair_content=random.choice(repair_contents),
#                     repair_cost=repair_cost,
#                     repair_duration=repair_duration,
#                     actual_completion_date=actual_completion_date,
#                     parts_replaced=random.choice(replaced_parts),
#                     proof=None,
#                     create_time=report_date,
#                     update_time=actual_completion_date
#                 )
#                 # 状态为已完成或已取消，设备设为非运行
#                 product.running_status = 0
#             new_records.append(record)

#         session.bulk_save_objects(new_records)
#         session.commit()
#         print(f"成功导入 {len(new_records)} 条维修记录")
#     except Exception as e:
#         session.rollback()
#         print(f"导入失败:{str(e)} ")
#     finally:
#         session.close()



def import_repair_records():
    db_path = os.path.abspath('instance/mis.db')
    engine = create_engine(f'sqlite:///{db_path}')
    Session = sessionmaker(bind=engine)
    session = Session()  # 新增会话初始化
    
    try:
        df = pd.read_excel(r'E:\huangpu4\黄浦区维修记录表中文表头.xlsx',
                          usecols=["org_shortname_c",'department_name', 'fault_description', 'report_date',
                                   'repair_caller', 'repair_answer_date', 'repair_type',
                                   'repair_content', 'repair_cost', 'repair_result',
                                   'parts_replaced', 'repair_person', 'repair_company',
                                   'actual_completion_date', 'repair_status', 'remarks',
                                   'product_name'],
                          dtype={'repair_cost': float})
        
        # 自动生成默认值字典，所有字段默认空字符串
        fill_values = {col: '' for col in df.columns}
        # 针对特殊字段单独赋值
        for key, val in {
            'repair_cost': 0.0,
            'repair_type': '常规维修',
            'repair_status': '已完成',
        }.items():
            if key in fill_values:
                fill_values[key] = val
        
        # 填充空值
        df = df.fillna(value=fill_values)
        print(f"P443 df={df}")
        
        # 初始化变量
        index = -1
        success_count = 0
        from sqlalchemy import func, Table, MetaData
        max_id = session.query(func.max(RepairRecord.id)).scalar() or 0
        
        # 获取表对象
        metadata = MetaData()
        repair_table = Table('repair_records', metadata, autoload_with=engine)
        
        # 定义要插入的字段
        insert_fields = [
            "org_shortname_c","product_name", "department_name", "fault_description", "report_date", 
            "repair_caller", "repair_answer_date", "repair_type", "repair_content", 
            "repair_cost", "repair_result", "parts_replaced", "repair_person", 
            "repair_company", "actual_completion_date", "repair_status", "remarks"
        ]
        
        for index, row in df.iterrows():
            try:
                # 只包含指定字段的数据，并确保数据不为空
                record_data = {}
                for k in insert_fields:
                    if k in row and pd.notna(row[k]):
                        # 对日期类型特殊处理
                        if k in ['report_date', 'repair_answer_date', 'actual_completion_date'] and pd.notna(row[k]):
                            record_data[k] = row[k]
                        # 对数值类型特殊处理
                        elif k == 'repair_cost':
                            record_data[k] = float(row[k]) if pd.notna(row[k]) else 0.0
                        # 其他字段
                        else:
                            record_data[k] = str(row[k]) if pd.notna(row[k]) else ''
                
                # 检查record_data是否为空
                if record_data:
                    # 使用insert()方法直接插入指定字段
                    stmt = repair_table.insert().values(**record_data)
                    session.execute(stmt)
                    success_count += 1
                else:
                    print(f"第{index+1}行数据为空，跳过")
            except Exception as e:
                print(f"p475导入失败(行{index+1}): {(str(e)[:1800])}")
                session.rollback()

        session.commit()
        print(f"成功导入 {success_count} 条维修记录")        
       
    except Exception as e:
        session.rollback()
        print(f"p475导入失败: {(str(e)[:1800])}")
    finally:
        session.close()





def import_maintain_data():
    db_path = os.path.abspath('instance/mis.db')
    engine = create_engine(f'sqlite:///{db_path}')
    Session = sessionmaker(bind=engine)
    session = Session()
    try:
        # 查询所有有效设备
        products = session.query(Product).filter_by(is_active=True).all()
        if not products:
            print("无可用设备数据")
            return

        records = []
        for _ in range(1000):
            product = random.choice(products)
            # 随机生成计划保养日期（2024-01-01 ~ 2024-12-31）
            scheduled_base = datetime(2025, 1, 1)
            scheduled_date = scheduled_base + timedelta(days=random.randint(0, 364))
            completion_status = random.choice(['已完成','已完成','已完成','已完成','已完成','已完成','已完成','已完成','已完成', '未完成'])
            # 判断完成状态，未完成则实际保养日期为空
            if completion_status == '未完成':
                actual_date = None
            else:
                actual_date = scheduled_date + timedelta(days=random.randint(1, 10))

            record = MaintainRecord(
                product_id=product.id,
                product_name=product.product_name,
                product_code=product.product_code,
                org_id=None,  # 如有需要可关联机构ID
                org_name=product.org_short_name_c if hasattr(product, 'org_short_name_c') else product.org_name,
                dept_name=product.department_name if hasattr(product, 'department_name') else "",
                scheduled_date=scheduled_date.date(),
                actual_date=actual_date.date() if actual_date else None,
                maintenance_type=random.choice(['例行保养', '专项保养', '紧急保养']),
                maintenance_content=random.choice(['清洁消毒', '结构检查', '润滑检查', '耗材更换']),
                maintenance_result=random.choice(['合格', '不合格', '待复查']),
                maintenance_person=random.choice(['工人甲', '工人乙', '工人丙']),
                maintenance_company=random.choice(['本公司', '原厂', '第三方']),
                completion_status=completion_status,
                remarks='自动生成数据',
                is_active=True
            )
            records.append(record)

        session.bulk_save_objects(records)
        session.commit()
        print("已成功导入1000条保养记录")
    except Exception as e:
        session.rollback()
        print(f"导入失败:{str(e)} ")
    finally:
        session.close()




def import_inspection_data():
    db_path = os.path.abspath('instance/mis.db')
    engine = create_engine(f'sqlite:///{db_path}')
    Session = sessionmaker(bind=engine)
    session = Session()
    try:
        products = session.query(Product).filter_by(is_active=True).all()
        if not products:
            print("无可用设备数据")
            return

        records = []
        for _ in range(1000):
            product = random.choice(products)
            # 随机生成计划巡检日期（2025-01-01 ~ 2025-12-31）
            plan_base = datetime(2025, 1, 1)
            scheduled_date = plan_base + timedelta(days=random.randint(0, 364))
            completion_status = random.choice(['已完成', '已完成', '已完成', '已完成', '已完成', '已完成', '已完成', '已完成', '未完成'])
            # 判断完成状态，未完成则实际巡检日期为空
            if completion_status == '未完成':
                actual_date = None
            else:
                actual_date = scheduled_date + timedelta(days=random.randint(1, 10))

            inspection_type = random.choice(['例行巡检', '专项巡检', '紧急巡检'])
            inspection_content = random.choice(['外观检查', '功能测试', '安全检测', '性能评估'])
            inspection_result = random.choice(['合格', '不合格', '需复查'])
            inspection_person = random.choice(['张三', '李四', '王五', '赵六'])
            inspection_company = random.choice(['本公司', '原厂', '第三方'])
            remarks = '自动生成数据'

            record = InspectionRecord(
                product_id=product.id,
                product_name=product.product_name,
                product_code=product.product_code,
                org_name=product.org_short_name_c if hasattr(product, 'org_short_name_c') else product.org_name,
                dept_name=product.department_name,
                scheduled_date=scheduled_date.date(),
                actual_date=actual_date.date() if actual_date else None,
                inspection_type=inspection_type,
                inspection_content=inspection_content,
                inspection_result=inspection_result,
                inspection_person=inspection_person,
                inspection_company=inspection_company,
                completion_status=completion_status,
                remarks=remarks,
                is_active=True
            )
            records.append(record)

        session.bulk_save_objects(records)
        session.commit()
        print("已成功导入1000条巡检记录")
    except Exception as e:
        session.rollback()
        print(f"导入失败:{str(e)} ")
    finally:
        session.close()




def export_org_device_stats():
    """
    统计每个机构的有效设备数量并导出Excel文件
    输出路径：modules/statistic/xlsx/各机构保养巡检次数统计.xlsx
    """
    import os
    from sqlalchemy import func
    from models import Product, Organization
    
    db_path = os.path.abspath('instance/mis.db')
    engine = create_engine(f'sqlite:///{db_path}')
    Session = sessionmaker(bind=engine)
    session = Session()
    
    try:
        # 查询每个机构的有效设备数量
        stats = session.query(
            Organization.org_name,
            Organization.short_name_c,
            func.count(Product.id).label('device_count')
        ).join(
            Product, Product.org_name == Organization.org_name
        ).filter(
            Product.is_active == True
        ).group_by(
            Organization.org_name
        ).all()
        
        # 转换为DataFrame
        df = pd.DataFrame([{
            '机构全称': stat.org_name,
            '机构简称': stat.short_name_c,
            '有效设备数量': stat.device_count
        } for stat in stats])
        
        # 确保输出目录存在
        output_dir = os.path.abspath('modules/statistic/xlsx')
        os.makedirs(output_dir, exist_ok=True)
        
        # 导出Excel
        output_path = os.path.join(output_dir, '各机构保养巡检次数统计.xlsx')
        df.to_excel(output_path, index=False)
        print(f'成功导出机构设备统计到: {output_path}')
        
        return True
    except Exception as e:
        print(f'导出失败: {str(e)}')
        return False
    finally:
        session.close()



# from sqlalchemy import create_engine
# from sqlalchemy.orm import sessionmaker
# from models import Product
# import pandas as pd
# import os
# from datetime import timedelta

# def import_product_2025():
#     db_path = os.path.abspath('instance/mis.db')
#     engine = create_engine(f'sqlite:///{db_path}')
#     Session = sessionmaker(bind=engine)
#     session = Session()
#     try:
#         file_path = os.path.abspath('E:\\huangpu4\\2025年黄浦区社区医院维保设备汇总(2).xlsx')
#         xls = pd.ExcelFile(file_path)
#         df_list = []
#         for sheet in xls.sheet_names:
#             df = pd.read_excel(xls, sheet_name=sheet)
#             df.columns = [str(col).strip() for col in df.columns]
#             df_list.append(df)
#         combined_df = pd.concat(df_list, ignore_index=True)
#         print('成功读取的列名:', list(combined_df.columns))
#         required_cols = ['org_name', 'product_name', 'manufacturer', 'model', 'serial_number', 'department_name', 'price']
#         for col in required_cols:
#             if col not in combined_df.columns:
#                 print(f'缺少必要字段: {col}')
#                 return
#         def safe_date(val):
#             # 新增空值处理
#             if pd.isna(val) or str(val).strip() in ('', 'NaT', 'nan'):
#                 return None
#             try:
#                 # 强制转换为datetime.date类型
#                 return pd.to_datetime(val).date() 
#             except Exception as e:
#                 print(f"无法转换日期值：{val}，错误：{str(e)}")
#                 return None

#         # 在查询条件构建处增加日期有效性检查
#         # if purchase_date and isinstance(purchase_date, date):
#         #     date_low = purchase_date - timedelta(days=1)
#         #     date_high = purchase_date + timedelta(days=1)
#         #     conditions.extend([
#         #         Product.purchase_date >= date_low,
#         #         Product.purchase_date <= date_high
#         #     ])
#         for idx, row in combined_df.iterrows():
#             # 先将所有字段转换为合适的类型，NaT/NaN转为None
#             def safe_val(val):
#                 if pd.isna(val):
#                     return None
#                 if isinstance(val, pd.Timestamp):
#                     return val.to_pydatetime().date()
#                 return val
#             product_name = safe_val(row['product_name'])
#             org_name = safe_val(row['org_name'])
#             org_short_name_c = safe_val(row.get('org_short_name_c', None))
#             department_name = safe_val(row['department_name'])
#             manufacturer = safe_val(row['manufacturer'])
#             model = safe_val(row['model'])
#             # purchase_date = safe_val(row['purchase_date'])
#             query = session.query(Product).filter(
#                 Product.product_name == product_name,
#                 (Product.org_name == org_name) | (Product.org_short_name_c == org_short_name_c),
#                 Product.department_name == department_name,
#                 Product.manufacturer == manufacturer,
#                 Product.model == model,
#             )
#             # if purchase_date:
#             #     query = query.filter(Product.purchase_date >= purchase_date - timedelta(days=1), Product.purchase_date <= purchase_date + timedelta(days=1))
#             product = query.first()
#             if product:
#                 # 可在此补充更新逻辑
#                 pass
#                    # 新增导出逻辑
#         output_path = os.path.join(os.getcwd(), 'output2025.xlsx')
#         try:
#             # 合并匹配和未匹配记录
#             matched_df = pd.DataFrame([
#             {**row, 'match_status': '已匹配', 'product_id': product.id}
#             for row, product in matched_records
#             ])
#             unmatched_df = pd.DataFrame([
#             {**row, 'match_status': '未匹配', 'product_id': None}
#             for row in unmatched_records
#             ])
            
#             merged_df = pd.concat([matched_df, unmatched_df], ignore_index=True)
            
#             # 调整列顺序
#             merged_df = merged_df[['match_status', 'product_id', 'org_name', 'product_name', 
#                                 'manufacturer', 'model', 'purchase_date', 'department_name']]
            
#             # 导出Excel
#             output_path = os.path.join(os.getcwd(), 'output2025.xlsx')
#             merged_df.to_excel(output_path, index=False)
#         except Exception as e:
#             print(f"导出文件失败：{str(e)}")
   
#     except Exception as e:
#         print('处理失败:', str(e))
#     finally:
#         session.close()
 
def make_product_excel_2025():
    from sqlalchemy import and_
    from models import Product
    import pandas as pd
    import os
    from sqlalchemy import create_engine
    from sqlalchemy.orm import sessionmaker

    # 添加在文件顶部
    engine = create_engine('sqlite:///instance/mis.db')
    Session = sessionmaker(bind=engine)
    session = Session()
    
    # 读取Excel文件（所有Sheet）
    excel_path = r'E:\huangpu4\2025年黄浦区社区医院维保设备汇总(2).xlsx'
    sheets = pd.read_excel(excel_path, sheet_name=None)
    all_data = pd.concat(sheets.values(), ignore_index=True)
    all_sheets = pd.read_excel(excel_path, sheet_name=None)
    df = pd.concat(all_sheets.values(), ignore_index=True)
    print(f'成功读取数据: 总行数 {len(df)}')
    # 定义条件递减顺序
    condition_levels = [
        ['product_name', 'manufacturer', 'model', 'department_name', 'org_name'],
        ['product_name', 'manufacturer', 'department_name', 'org_name'],
        ['product_name', 'manufacturer', 'org_name'],
        ['product_name', 'org_name'],
        ['product_name']
    ]

    results = []

    for idx, row in all_data.iterrows():
        # 清洗数据
        clean_row = {col: str(val).strip() if pd.notna(val) else None 
                    for col, val in row.items()}
        
        product = None
        
        # 按条件优先级尝试匹配
        for condition in condition_levels:
            try:
                filters = []
                for field in condition:
                    if clean_row.get(field):
                        filters.append(getattr(Product, field) == clean_row[field])
                
                if filters:
                    product = session.query(Product).filter(and_(*filters)).first()
                    if product:
                        break
            except Exception as e:
                print(f'条件{condition}查询异常: {str(e)}')

        # 补充数据
        if product:
            clean_row.update({
                'equipment_category': product.equipment_category,
                'service_life': product.service_life,
               
            })
        else:
            clean_row.update({
                'equipment_category': None,
                'service_life': None,
               
            })
        
        results.append(clean_row)

    # 生成结果DataFrame
    result_df = pd.DataFrame(results)
    print(f'准备导出数据: 有效行数 {len(result_df)}')
    # 保持原始列顺序并添加新列
    output_columns = all_data.columns.tolist() + [
        'equipment_category',
        'service_life',
        
    ]
    
    # 导出文件
    output_path = os.path.join(os.getcwd(), 'huangpu2025epuipments.xlsx')
    result_df[output_columns].to_excel(output_path, index=False)
    
    session.close()
    print( f'处理完成，输出文件已保存至：{output_path}')


def import_to_product():
    from models import Product
    from sqlalchemy.exc import IntegrityError
    from sqlalchemy import func
    
    engine = create_engine('sqlite:///instance/mis.db')
    Session = sessionmaker(bind=engine)
    session = Session()

    try:
        # 读取并预处理数据
        df = pd.read_excel(r'E:\huangpu4\huangpu\huangpu2025epuipments.xlsx')
        df['service_life'] = df['service_life'].fillna(6)
        print(f'成功读取 {len(df)} 条记录')

        # 获取当前最大ID
        max_id = session.query(func.max(Product.id)).scalar() or 0
        current_id = max_id + 1

        success = 0
        for _, row in df.iterrows():
            try:
                product = Product(
                    id=current_id,
                    org_name=row['org_name'],
                    product_name=row['product_name'],
                    manufacturer=row['manufacturer'],
                    model=row['model'],
                    serial_number=row['serial_number'],
                    department_name=row['department_name'],
                    price=float(row['price']) if pd.notnull(row['price']) else 0.0,
                    purchase_date=pd.to_datetime(row['purchase_date']).date() if pd.notnull(row['purchase_date']) else None,
                    
                    equipment_category=row['equipment_category'],
                    service_life=int(row['service_life']) if pd.notnull(row['service_life']) else 6,
                    org_short_name_c=row['org_short_name_c'],
                    is_active=1  # 设置默认值
                )
                session.add(product)
                current_id += 1
                success += 1
            except IntegrityError as e:
                print(f'重复记录跳过: {row["product_name"]}')
                session.rollback()

        session.commit()
        print(f'成功插入 {success}/{len(df)} 条记录，最后生成的ID为 {current_id-1}')

    except Exception as e:
        session.rollback()
        print(f'发生错误: {str(e)}')
    finally:
        session.close()

if __name__ == "__main__":
    
   
    # import_org_data()
    # import_department_data()
    # import_to_product()
    # import_repair_data()
    # import_repair_data2()
    # import_maintain_data()
    # import_inspection_data()
    # export_org_device_stats()
    # make_product_excel_2025()
    import_repair_records()
    pass

    

  

        